package com.kun.dao.user;

import com.kun.dao.BaseDao;
import com.kun.dao.role.RoleDaoImpl;
import com.kun.entity.User;
import com.mysql.jdbc.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class UserDaoImpl implements UserDao {
    //得到要登录的用户
    public User getLoginUser(Connection connection, String userCode) throws SQLException {

        PreparedStatement ps = null;
        ResultSet rs = null;
        User user = null;

        if(connection != null) {
            String sql = "select * from smbms_user where userCode = ?";
            Object[] params = {userCode};

            rs = BaseDao.executeQuery(connection, ps, rs, sql, params);

            if (rs.next()) {
                user = createUserFromDB(rs);
            }

            BaseDao.closeResource(ps,rs);
        }
        return user;
    }

    //修改当前用户密码
    public int updatePwd(Connection connection, long id, String pwd) throws SQLException {
        PreparedStatement ps = null;
        int execute = 0;

        if(connection != null){
            String sql = "update smbms_user set userPassword = ? where id = ?";
            Object[] params = {pwd,id};
            execute = BaseDao.executeUpdate(connection,ps,sql,params);
            BaseDao.closeResource(ps);
        }

        return execute;
    }

    //查询用户总数(根据用户名或者角色查询用户总数)
    public int getUserCount(Connection connection, String userName, int userRole) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;

        if(connection != null){
            StringBuilder sql = new StringBuilder();
            ArrayList<Object> params = new ArrayList<Object>();
            //默认查询所有用户的数量
            sql.append("SELECT COUNT(1) AS COUNT FROM smbms_user u JOIN smbms_role r ON u.userRole = r.id ");

            //0为任意角色，大于0则指定一个角色
            if(userRole > 0){
                sql.append("AND r.id = ? ");
                params.add(userRole);
            }

            //指定了 名字 模糊查询参数
            if(!StringUtils.isNullOrEmpty(userName)){
                sql.append("AND u.userName LIKE ? ");
                params.add("%"+userName+"%");
            }

            System.out.println(sql.toString());

            rs = BaseDao.executeQuery(connection,ps,rs,sql.toString(),params.toArray());

            if(rs.next()){
                count = rs.getInt("count");
            }

            BaseDao.closeResource(ps,rs);
        }

        return count;
    }

    /**
     * 查询用户信息(根据用户名或者角色查询用户信息)
     * @param queryUserName 模糊查询的用户名
     * @param queryUserRole 用户所属角色号 0-->任意角色
     * @param currentPageNo 当前页码
     * @param pageSize      每页展示条数
     * @return              用户列表
     * @throws SQLException
     */
    public ArrayList<User> getUserList(Connection connection,String queryUserName, int queryUserRole, int currentPageNo, int pageSize) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<User> list = new ArrayList<User>();

        if(connection != null){
            StringBuilder sql = new StringBuilder();
            ArrayList<Object> params = new ArrayList<Object>();
            //默认查询所有用户的数量
            sql.append("SELECT u.* FROM smbms_user u JOIN smbms_role r ON u.userRole = r.id ");

            //0为任意角色，大于0则指定一个角色
            if(queryUserRole > 0){
                sql.append("AND r.id = ? ");
                params.add(queryUserRole);
            }

            //指定了 名字 模糊查询参数
            if(!StringUtils.isNullOrEmpty(queryUserName)){
                sql.append("AND u.userName LIKE ? ");
                params.add("%"+queryUserName+"%");
            }

            //limit  根据creationDate排序后 ， 设置查询的数据范围
            sql.append("ORDER BY u.creationDate DESC LIMIT ?,?;");
            params.add((currentPageNo-1)*pageSize);//查询的起始位置
            params.add(pageSize);//一页查询的数量

//            System.out.println("sql-->"+sql.toString());

            rs = BaseDao.executeQuery(connection,ps,rs,sql.toString(),params.toArray());

            while (rs.next()){
                list.add(createUserFromDB(rs));
            }

            BaseDao.closeResource(ps,rs);
        }

        return list;
    }

    //添加用户
    public int addUser(Connection connection,User user)throws SQLException{
        PreparedStatement ps = null;
        int updateRows = 0;

        if(connection != null && user != null){
            String sql = "INSERT INTO smbms_user(USERCODE, USERNAME, USERPASSWORD, GENDER, BIRTHDAY, PHONE, ADDRESS, USERROLE, CREATEDBY, CREATIONDATE) \n" +
                    "VALUES (?,?,?,?,?,?,?,?,?,?);";

            Object[] params = {
                    user.getUserCode(),
                    user.getUserName(),
                    user.getUserPassword(),
                    user.getGender(),
                    user.getBirthday(),
                    user.getPhone(),
                    user.getAddress(),
                    user.getUserRole(),
                    user.getCreatedBy(),
                    user.getCreationDate()
            };
            updateRows = BaseDao.executeUpdate(connection,ps,sql,params);
            BaseDao.closeResource(ps);
        }

        return updateRows;
    }

    //查询一个用户（ID或UserCode）
    public User getUser(Connection connection,Long id,String userCode)throws SQLException{
        PreparedStatement ps = null;
        User user = null;
        ResultSet rs = null;

        if(connection != null){
            StringBuilder sql = new StringBuilder();
            Object[] params = new Object[1];
            sql.append("SELECT * FROM smbms_user WHERE ");

            if(id != null){
                sql.append("id = ? ");
                params[0] = id;
            }else if(userCode != null){
                sql.append("userCode = ? ");
                params[0] = userCode;
            }else {//没有参数直接返回null
                return null;
            }

//            System.out.println(sql.toString());

            rs = BaseDao.executeQuery(connection,ps,rs,sql.toString(),params);

            if(rs.next()){
                user = createUserFromDB(rs);
            }

            BaseDao.closeResource(ps,rs);
        }

        return user;
    }

    //删除一个用户（ID或UserCode）
    public int delUser(Connection connection,Long id,String userCode)throws SQLException{
        PreparedStatement ps = null;
        int delRows = 0;
        if(connection != null){
            StringBuilder sql = new StringBuilder("DELETE FROM smbms_user WHERE ");
            Object[] params = new Object[1];
            if(id != null){
                sql.append("id = ? ");
                params[0] = id;
            }else if(userCode != null){
                sql.append("userCode = ? ");
            }else {//参数不存在
                return 0;
            }
            delRows = BaseDao.executeUpdate(connection,ps,sql.toString(),params);
            BaseDao.closeResource(ps);
        }
        return delRows;
    }

    //修改用户信息
    @Override
    public int modifyUser(Connection connection,User user)throws SQLException{
        PreparedStatement ps = null;
        int modifyRows = 0;
        if(connection != null && user != null){
            String sql = "UPDATE smbms_user\n" +
                    "SET userName = ?,gender = ?,birthday = ?,phone = ?,address = ?,userRole = ?,modifyBy = ?,modifyDate = ?\n" +
                    "WHERE id = ?;";
            Object[] params = {
                user.getUserName(),
                user.getGender(),
                user.getBirthday(),
                user.getPhone(),
                user.getAddress(),
                user.getUserRole(),
                user.getModifyBy(),
                user.getModifyDate(),

                user.getId()
            };
            modifyRows = BaseDao.executeUpdate(connection,ps,sql,params);
            BaseDao.closeResource(ps);
        }
        return modifyRows;
    }

    private User createUserFromDB(ResultSet rs) throws SQLException {
        User user = new User();
        user.setId(rs.getLong("id"));
        user.setUserCode(rs.getString("userCode"));
        user.setUserName(rs.getString("userName"));
        user.setUserPassword(rs.getString("userPassword"));
        user.setGender(rs.getInt("gender"));
        user.setBirthday(rs.getDate("birthday"));
        user.setPhone(rs.getString("phone"));
        user.setAddress(rs.getString("address"));
        user.setUserRole(rs.getInt("userRole"));
        user.setCreatedBy(rs.getInt("createdBy"));
        user.setCreationDate(rs.getTimestamp("creationDate"));
        user.setModifyBy(rs.getInt("modifyBy"));
        user.setModifyDate(rs.getTimestamp("modifyDate"));
        return user;
    }
}

